package nc.impl.er.expapprove;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import nc.bs.dao.BaseDAO;
import nc.bs.dao.DAOException;
import nc.bs.er.expapprove.util.YerExpenseApproveUtil;
import nc.bs.er.expportal.util.YerExpPortalUtil;
import nc.bs.er.util.YerPortalUtil;
import nc.bs.framework.common.InvocationInfoProxy;
import nc.bs.framework.common.NCLocator;
import nc.bs.logging.Logger;
import nc.bs.pf.pub.PfDataCache;
import nc.itf.er.expapprove.IExpenseApprovePortalService;
import nc.itf.er.query.IYerQueryService;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.processor.BaseProcessor;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.jdbc.framework.processor.ResultSetProcessor;
import nc.newinstall.util.StringUtil;
import nc.ssc.pub.util.SscIntergrationUtil;
import nc.uap.lfw.core.data.PaginationInfo;
import nc.uap.lfw.core.exception.LfwRuntimeException;
import nc.uap.lfw.ncadapter.crud.LfwBeanPagedListProcessor;
import nc.uap.lfw.ncadapter.crud.PagedResult;
import nc.uap.portal.log.PortalLogger;
import nc.uap.wfm.utils.AppUtil;
import nc.util.fi.pub.SqlUtils;
import nc.vo.er.expportal.PortalQueryFieldName;
import nc.vo.er.task.BillApproveNav;
import nc.vo.er.task.YerTaskQueryParamVO;
import nc.vo.er.task.YerTaskVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.SuperVO;
import nc.vo.pub.lang.UFDate;

import org.apache.commons.lang.StringUtils;

public class ExpenseApprovePortalServiceImpl implements
		IExpenseApprovePortalService {
	private static String REMOVELIST = "removeList";
	private static String APPROVERESULT = "approveResult";

	/**
	 * 根据传入的参数获取报销待办信息
	 */
	@Override
	public YerTaskVO[] queryMyBillVOs(YerTaskQueryParamVO paramVO)
			throws BusinessException {
		PaginationInfo pinfo = paramVO.getPinfo();
		StringBuffer querySql = new StringBuffer();
		StringBuffer whereSql = new StringBuffer();
		querySql.append("select * from (");
		querySql.append(getBillApproveSql(paramVO, false, null));
		querySql.append(")  a ");
		// houmeng3+模糊搜索修改为单据编号，日期，金额
		if (StringUtils.isNotEmpty(paramVO.getLikeQueryValue())) {
			String likeQueryValue = paramVO.getLikeQueryValue();
			String[] queryValues = likeQueryValue.split("&&");
			for(String value :queryValues){
				if (value != null && !"".equals(value.trim())) {
					if (value.startsWith("NO")) {
						value = value.replace("NO", "").trim();
						whereSql.append(" and a.billno like '%"+value+"%' ");
					} else if (value.startsWith("PSN")) {
						value = value.replace("PSN", "").trim();
						whereSql.append(" and a.defitem5 like '%"+value+"%' ");
					} else if (value.startsWith("DEPT")) {
						value = value.replace("DEPT", "").trim();
						whereSql.append(" and a.defitem6 like '%"+value+"%' ");
					}
				}
				
			}
			querySql.append(" where ( 1=1 "+ whereSql.toString()+ ")\n");
		}
		querySql.append(" order by a.receivetime desc");

//		List<SuperVO> voList = YerExpenseApproveUtil.getVOBySql(pinfo,
//				querySql.toString(), YerTaskVO.class);
		List<SuperVO> voList = getVOBySql(pinfo,querySql.toString(), YerTaskVO.class);
//		Map<Integer,String> arapSrcSys = YerExpenseApproveUtil.getArapSrcSys();
		if (StringUtils.isNotEmpty(paramVO.getLikeQueryValue())) {
			for (SuperVO vo : voList) {
				YerTaskVO taskVo = (YerTaskVO) vo;
				String title = YerPortalUtil.getMessageNoteAfterI18N(taskVo
						.getTitle());
				taskVo.setTitle(title);
				taskVo.setTradetypename(YerPortalUtil.getBilltypeNameMultiLang(taskVo
						.getBilltype()));
//				taskVo.setDefitem11(YerExpPortalUtil.getSrcSys(taskVo.getBilltype(), arapSrcSys,taskVo.getBillid()));
			}
			return voList.toArray(new YerTaskVO[voList.size()]);
		}
		Set<String> billtypes = new HashSet<String>();
		Set<String> pks = new HashSet<String>();

		if (voList != null && voList.size() > 0) {
			for (SuperVO superVO : voList) {
				String billtype = (String) superVO
						.getAttributeValue("billtype");
				
				if (!StringUtils.isEmpty(billtype)) {
					if("D0".equals(billtype) || "D1".equals(billtype) || "D2".equals(billtype) || "D3".equals(billtype)){
						billtypes.add(billtype);
						billtypes.add("F"+billtype.substring(1, 2));
					}else{
						billtypes.add(YerExpPortalUtil.getDjdl(billtype));
					}
				}
				// String billid = (String) superVO.getAttributeValue("billid");
				String pk_checkflow = (String) superVO
						.getAttributeValue("defitem7");
				if (!StringUtils.isEmpty(billtype)) {
					pks.add(pk_checkflow);
				}
			}

			// 重新查询结果
			StringBuffer querySqlDetail = new StringBuffer();
			querySqlDetail.append("select * from (");
			querySqlDetail.append(getBillApproveSql(paramVO, true, billtypes));
			querySqlDetail.append(")  a where "
					+ SqlUtils.getInStr("defitem7", pks.toArray(new String[0]))
					+ " order by a.receivetime desc");

//			voList = YerExpenseApproveUtil.getVOBySql(querySqlDetail.toString(),
//					YerTaskVO.class);
			voList=(List<SuperVO>) executeQuery(querySqlDetail.toString(), new BeanListProcessor(YerTaskVO.class));
		}

		// 转译title字段以及交易类型字段
		for (SuperVO vo : voList) {
			YerTaskVO taskVo = (YerTaskVO) vo;
			String title = YerPortalUtil.getMessageNoteAfterI18N(taskVo
					.getTitle());
			taskVo.setTitle(title);
			taskVo.setTradetypename(YerPortalUtil.getBilltypeNameMultiLang(taskVo
					.getBilltype()));
//			taskVo.setDefitem11(YerExpPortalUtil.getSrcSys(taskVo.getBilltype(), arapSrcSys,taskVo.getBillid()));
		}
		return voList.toArray(new YerTaskVO[voList.size()]);
	}

	/**
	 * 获得总的已办数
	 */
	@Override
	public List<BillApproveNav> queryIsApprovedCountList() {
		StringBuffer querySql = new StringBuffer();
		StringBuffer whereTypeSql = new StringBuffer();
		List<BillApproveNav> result = null;
		querySql.append(
				"select billtype.PK_BILLTYPECODE as billTypeCode,billtype.billstyle as djdl, billtype.BILLTYPENAME as billTypeName,count(1) as approveCount\n")
				.append("from pub_workflownote note\n")
				.append("join bd_billtype billtype on note.PK_BILLTYPE=billtype.PK_BILLTYPECODE\n")
				.append("where note.actiontype like 'Z%'\n")
				// .append("and note.workflow_type in (2,3) \n")
				.append("and note.checkman='" + getReceiver()
						+ "' and note.approvestatus in(1)\n")
				// .append("and billtype.PK_GROUP='" + YerUtil.getPK_group() +
				// "'\n")
				.append("and (billtype.PK_GROUP='" + YerPortalUtil.getPK_group()
						+ "' or billtype.PK_GROUP='~')\n")// houmeng3+
				// .append("and billtype.islock='N'\n");
				.append("and (billtype.islock ='N' or billtype.islock is null)\n");// houmeng3+
		String linkId = (String) AppUtil.getAppAttr("EALINKID");
		if (linkId != null && !"".equals(linkId)) {
			UFDate[] date = YerExpenseApproveUtil.getBillDates(linkId);
			querySql.append(" and  note.ts >='"
					+ date[0].toLocalString().substring(0, 10) + "'\n");
		} else if (linkId == null) {// houmeng3+
			UFDate[] date = YerExpenseApproveUtil
					.getBillDates(YerExpenseApproveUtil.LINK_ONEWEEK);
			querySql.append(" and  note.ts >='"
					+ date[0].toLocalString().substring(0, 10) + "'\n");
		}
		
		// jiaogh针对单产品测试修改
		//querySql.append("and (");
		
		// Map<String, String> billTypeTableMap =
		// ExpenseApproveUtil.getBillTypeToMasterTable();
		Iterator<Entry<String, PortalQueryFieldName>> ite = YerExpPortalUtil
				.getCodeToQueryFieldMap().entrySet().iterator();
		while (ite.hasNext()) {
			String key = ite.next().getKey();
			if (whereTypeSql != null && whereTypeSql.length() > 0) {
				whereTypeSql.append(" or ");
			}
			if (key != null && "36D1".equals(key)) {
				whereTypeSql// 付款申请单 轻量端与重量端数据隔离 houmeng3+
						.append(" (billtype.pk_billtypecode like'"
								+ key
								+ "%' and exists (select opsrctype from cmp_apply where cmp_apply.pk_apply=note.billid and opsrctype=1))");

			} else {
				if("F0".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'D0' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("F1".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'D1' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("F2".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'D2' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("F3".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'D3' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("FIV".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'FP0' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("264".equals(key)){
					whereTypeSql.append(" billtype.pk_billtypecode in (select djlxbm from er_djlx where djlxbm like '264%' and bxtype !='2')");//去掉调整单
				}
				//去掉预提单的限制
//				else if("262".equals(key)){
//					//去掉费用预提单 qiufh添加
//					whereTypeSql.append(" 1<>1 ");
//				}
				else{
				    whereTypeSql.append(" (billtype.pk_billtypecode like'" + key
						+ "%' ) ");
				}
			}

		}
		
		
		// jiaogh针对单产品测试修改
		//querySql.append(whereTypeSql);
		
		if (whereTypeSql.length() > 0) {
			querySql.append(" and ( " + whereTypeSql.toString() + " ) ");
		}
		
		querySql.append(
				" group by billtype.PK_BILLTYPECODE,billtype.BILLTYPENAME,billtype.billstyle\n")
				.append("order by billtype.PK_BILLTYPECODE");
		try {
			BaseProcessor baseProcessor = new BeanListProcessor(
					BillApproveNav.class);
			
//			if(SscIntergrationUtil.isSscDeploy()){
//				result = (List<BillApproveNav>)NCLocator.getInstance().lookup(IYerQueryService.class).executeQuery(querySql.toString(), baseProcessor);
//			}else{
//				BaseDAO dao = new BaseDAO();
//				result = (List<BillApproveNav>) dao.executeQuery(
//						querySql.toString(), baseProcessor);
//			}
			result = (List<BillApproveNav>) executeQuery(querySql.toString(), baseProcessor);
			Map<String, List<BillApproveNav>> resultMap = filterMEData(result);// 适配供应链单据
			result.removeAll(resultMap.get(REMOVELIST));
			result.addAll(resultMap.get(APPROVERESULT));
			
		} catch (BusinessException e) {
			PortalLogger.error(e.getMessage(), e);
			throw new LfwRuntimeException(e.getMessage(), e);
		}

		return result;
	}

	/**
	 * 获得待办信息交易类型以及待办数
	 * @throws BusinessException 
	 */
	@Override
	public List<BillApproveNav> queryBillApproveList() throws BusinessException {

		StringBuffer querySql = new StringBuffer();
		StringBuffer whereTypeSql = new StringBuffer();
		List<BillApproveNav> result = null;
		querySql.append(
				"select billtype.PK_BILLTYPECODE as billTypeCode,billtype.billstyle as djdl, billtype.BILLTYPENAME as billTypeName,count(1) as approveCount\n")
				.append("from pub_workflownote note\n")
				.append("join bd_billtype billtype on note.PK_BILLTYPE=billtype.PK_BILLTYPECODE\n")
				.append("where note.actiontype like 'Z%'\n")
				// or note.actiontype like 'MAKEBILL%')
				// .append("and note.workflow_type in (2,3) \n")
				.append("and note.checkman='" + getReceiver()
						+ "' and note.approvestatus in(0,2)\n")
				.append("and (billtype.PK_GROUP='" + YerPortalUtil.getPK_group()
						+ "' or billtype.PK_GROUP='~')\n")// houmeng3
				// .append("and billtype.PK_GROUP='" + YerUtil.getPK_group()
				// + "'\n")
				// .append("and billtype.islock='N'\n");
				.append("and (billtype.islock ='N' or billtype.islock is null)\n");// houmeng3+
		String linkId = (String) AppUtil.getAppAttr("EALINKID");
		if (linkId != null && !"".equals(linkId)) {
			UFDate[] date = YerExpenseApproveUtil.getBillDates(linkId);
			querySql.append(" and  note.ts >='"
					+ date[0].toLocalString().substring(0, 10) + "'\n");
		} else if (linkId == null) {
			UFDate[] date = YerExpenseApproveUtil
					.getBillDates(YerExpenseApproveUtil.LINK_ONEWEEK);
			querySql.append(" and  note.ts >='"
					+ date[0].toLocalString().substring(0, 10) + "'\n");
		}
		// jiaogh针对单产品测试修改
		//querySql.append("and (");
		
		// Map<String, String> billTypeTableMap =
		// ExpenseApproveUtil.getBillTypeToMasterTable();
		// Iterator<String> ite = billTypeTableMap.keySet().iterator();
		Iterator<Entry<String, PortalQueryFieldName>> ite = YerExpPortalUtil
				.getCodeToQueryFieldMap().entrySet().iterator();
		while (ite.hasNext()) {
			String key = ite.next().getKey();
			if (whereTypeSql != null && whereTypeSql.length() > 0) {
				whereTypeSql.append(" or ");
			}
			if (key != null && "36D1".equals(key)) {
				whereTypeSql// 付款申请单 轻量端与重量端数据隔离
						.append(" (billtype.pk_billtypecode like'"
								+ key
								+ "%' and exists (select opsrctype from cmp_apply where cmp_apply.pk_apply=note.billid and opsrctype=1))");

			} else {
				if("F0".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'D0' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("F1".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'D1' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("F2".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'D2' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("F3".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'D3' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("FIV".equals(key)){
					whereTypeSql.append(" (billtype.pk_billtypecode = 'FP0' or billtype.pk_billtypecode like'" + key + "%' ) ");
				}else if("264".equals(key)){
					whereTypeSql.append(" billtype.pk_billtypecode in (select djlxbm from er_djlx where djlxbm like '264%' and bxtype !='2')");//去掉调整单
				}
				//去掉限制
//				else if("262".equals(key)){
//					//去掉费用预提单 qiufh添加
//					whereTypeSql.append(" 1<>1 ");
//				}
				else{
					whereTypeSql.append(" (billtype.pk_billtypecode like'" + key + "%' ) ");
				}
			}

		}
		
		// jiaogh针对单产品测试修改
		//querySql.append(whereTypeSql);
		
		if (whereTypeSql.length() > 0) {
			querySql.append(" and ( " + whereTypeSql.toString() + " ) ");
		}
		
		querySql.append(
				" group by billtype.PK_BILLTYPECODE,billtype.BILLTYPENAME,billtype.billstyle\n")
				.append("order by billtype.PK_BILLTYPECODE");
		try {
			BaseProcessor baseProcessor = new BeanListProcessor(
					BillApproveNav.class);
//			BaseDAO dao = new BaseDAO();
//			QueryUtil dao = new QueryUtil();
//			result = (List<BillApproveNav>) dao.executeQuery(
//					querySql.toString(), baseProcessor);
			result = (List<BillApproveNav>) executeQuery(querySql.toString(), baseProcessor);
			Map<String, List<BillApproveNav>> resultMap = filterMEData(result);// 适配供应链单据
			result.removeAll(resultMap.get(REMOVELIST));
			result.addAll(resultMap.get(APPROVERESULT));
		} catch (DAOException e) {
			PortalLogger.error(e.getMessage(), e);
			throw new LfwRuntimeException(e.getMessage(), e);
		}
		return result;
	}

	/**
	 * 获取当前登录用户id
	 * 
	 * @return
	 */
	public String getReceiver() {
		return InvocationInfoProxy.getInstance().getUserId();
	}

	// 生成待办查询sql
	private String getBillApproveSql(YerTaskQueryParamVO param,
			Boolean isDetail, Set<String> billtypes) {
		// Map<String, String> billTypeTableMap =
		// ExpenseApproveUtil.getBillTypeToMasterTable();
		Map<String, PortalQueryFieldName> map = YerExpPortalUtil
				.getCodeToQueryFieldMap();
		Iterator<Entry<String, PortalQueryFieldName>> ite = map.entrySet()
				.iterator();
		StringBuffer querySql = new StringBuffer();
		StringBuffer queryBillSql = new StringBuffer();
		Map<String, String> shortValuesMap = param.getShortValuesMap();
		if ("000".equals(param.getBillTypeCode())) {// 查询全部待办
			// Iterator<String> ite = billTypeTableMap.keySet().iterator();
			if (isDetail || StringUtils.isNotEmpty(param.getLikeQueryValue())) {
				while (ite.hasNext()) {
					Entry<String, PortalQueryFieldName> entry = ite.next();
					String key = entry.getKey();
					if (shortValuesMap != null && shortValuesMap.size() > 0) {
						if (shortValuesMap.get(key) == null) {
							continue;
						}
					}
					if (billtypes != null && !billtypes.contains(key)) {
						continue;
					}
					if (queryBillSql != null && queryBillSql.length() > 0) {
						queryBillSql.append("union\n");
					}
					queryBillSql.append(getParamWhereSql(param, key, map, "",
							shortValuesMap, isDetail));

				}
			} else {
				queryBillSql.append(getParamWhereSql(param, map, "",
						shortValuesMap, isDetail));
			}
		} else if ("show".equals(param.getDjdl())) {// 点击其他查询
			String[] billTypeCodes = param.getBillTypeCode().split(",");
			String key = "";
			for (int i = 0; i < billTypeCodes.length; i++) {
				if (billTypeCodes[i] != null) {
					// key = billTypeCodes[i].substring(0, 3);
					key = YerExpPortalUtil.getDjdl(billTypeCodes[i]);// houmeng3+
					// if(queryBillSql != null && queryBillSql.length() > 0) {
					// queryBillSql.append("union\n");
					// }
					// queryBillSql.append(getParamWhereSql(param,key,billTypeCodes[i],null));
					// } else {
					// key = billTypeCodes[i];
				}
				if (queryBillSql != null && queryBillSql.length() > 0) {
					queryBillSql.append("union\n");
				}
				queryBillSql.append(getParamWhereSql(param, key, map,
						billTypeCodes[i], null, isDetail));
			}

		} else {// 根据传入单据类型查询
			// String key = param.getDjdl();
			// if (key == null || "".equals(key) || "null".equals(key)) {
			// if (param.getBillTypeCode() != null
			// && param.getBillTypeCode().length() >= 3) {
			// key = param.getBillTypeCode().substring(0, 3);
			// } else {
			// key = param.getBillTypeCode();
			// }
			// }
			String key = YerExpPortalUtil.getDjdl(param.getBillTypeCode());// houmeng3+
			queryBillSql.append(getParamWhereSql(param, key, map,
					param.getBillTypeCode(), shortValuesMap, isDetail));

		}

		querySql.append(queryBillSql);

		return querySql.toString();
	}

	// 根据条件生成查询sql
	public String getParamWhereSql(YerTaskQueryParamVO param, String key,
			Map<String, PortalQueryFieldName> map, String billTypeCode,
			Map<String, String> shortValuesMap, Boolean isDetail) {
		// Map<String, String> billTypeTableMap =
		// ExpenseApproveUtil.getBillTypeToMasterTable();
		// Map<String, String> billQueryFieldMap =
		// ExpenseApproveUtil.getBillTypeToQueryField();
		// Map<String, String> billIDFieldMap =
		// ExpenseApproveUtil.getBillIDField();
		// Map<String, String> billMakerFieldMap =
		// ExpenseApproveUtil.getBillMakerField();
		// Map<String, String> billDeptFieldMap =
		// ExpenseApproveUtil.getBillDeptField();
		String tableName = map.get(key).getmTableName().contains("left") ? map
				.get(key).getmTableName().split(" ")[1] : map.get(key)
				.getmTableName();

		String selectField = "select yer_task.billno,yer_task.dr,yer_task.billid as billid,billtype.pk_billtypeid as defitem3,billtype.PK_BILLTYPECODE as billtype,"
				+ "yer_task.pk_wf_task as defitem2,yer_task.ts as receivetime, yer_task.pk_checkflow as defitem7 ";
		if (isDetail || StringUtils.isNotEmpty(param.getLikeQueryValue())) {
			selectField += ",yer_task.approvestatus as approvestatus,yer_task.messagenote as title,org.name as defitem4,"
					+ " psn.name as defitem5,dept.name as defitem6,yer_task.workflow_type as workflowtype,yer_task.actiontype as actiontype,";
			if ("freebillTypeCode".equals(map.get(key).getCode())) {
				selectField += "'1002Z0100000000001K1' as defitem10,";
			} else {
				if(map.get(key).getCurrtype()!=null && map.get(key).getCurrtype().equals("none")){
					selectField+="'' as defitem10,";
				} else {
					selectField += tableName
						+ "."
						+ (map.get(key).getCurrtype() == null ? "pk_currtype"
								: map.get(key).getCurrtype())
						+ " as defitem10,";
				}
			}
			selectField = selectField
					+ YerExpPortalUtil.getApproveQueryField(map.get(key));
		}

		StringBuffer whereSql = new StringBuffer();

		whereSql.append("select * from (");
		whereSql.append(selectField + " from pub_workflownote yer_task\n")
				.append("join bd_billtype billtype on yer_task.PK_BILLTYPE=billtype.PK_BILLTYPECODE\n");
		if (isDetail || StringUtils.isNotEmpty(param.getLikeQueryValue())) {
			whereSql.append("join " + tableName + " " + tableName)
					.append(" on yer_task.billid=" + tableName + "."
							+ map.get(key).getPk_id() + "\n")
					.append("left join org_adminorg  org on org.pk_adminorg="
							+ tableName + ".pk_org\n")
					.append("left join bd_psndoc  psn on psn.pk_psndoc="
							+ tableName + "." + map.get(key).getBillMaker()
							+ "\n")
					.append("left join org_dept  dept on dept.pk_dept="
							+ (StringUtil.isEmpty(map.get(key).getDeptId()) ? "'-100'"
									: tableName + "."
											+ map.get(key).getDeptId()) + "\n");
		}

		whereSql.append("where  yer_task.actiontype like 'Z%'\n")
		// .append("and yer_task.workflow_type in (2,3) \n")
				.append("and yer_task.checkman='" + getReceiver() + "'\n")
				// .append("and billtype.PK_GROUP='" + YerUtil.getPK_group()
				// + "'\n")
				.append("and (billtype.PK_GROUP='" + YerPortalUtil.getPK_group()
						+ "' or billtype.PK_GROUP ='~') \n");
		if (key.equals("4641") || key.equals("4642")) {// zhangjxh+
			whereSql.append("and (billtype.pk_billtypecode like '" + key
					+ "%' or billtype.pk_billtypecode like '261X%')\n");
		} else {
			if("F0".equals(key)){
				whereSql.append("and (billtype.pk_billtypecode = 'D0' or billtype.pk_billtypecode like '" + key + "%')\n");
			}else if("F1".equals(key)){
				whereSql.append("and (billtype.pk_billtypecode = 'D1' or billtype.pk_billtypecode like '" + key + "%')\n");
			}else if("F2".equals(key)){
				whereSql.append("and (billtype.pk_billtypecode = 'D2' or billtype.pk_billtypecode like '" + key + "%')\n");
			}else if("F3".equals(key)){
				whereSql.append("and (billtype.pk_billtypecode = 'D3' or billtype.pk_billtypecode like '" + key + "%')\n");
			}else if("FIV".equals(key)){
				whereSql.append("and (billtype.pk_billtypecode = 'FP0' or billtype.pk_billtypecode like '" + key + "%')\n");
			}else if("264".equals(key)){
				whereSql.append("and billtype.pk_billtypecode in (select djlxbm from er_djlx where djlxbm like '264%' and bxtype !='2')");//去掉调整单
			}
//			else if("262".equals(key)){
//				//去掉费用预提单 qiufh添加
//				whereSql.append("and 1<>1 ");
//			}
			else{
				whereSql.append("and billtype.pk_billtypecode like '" + key + "%'\n");
			}
		}

		if (key != null && "36D1".equals(key)) {
			whereSql.append("and exists (select opsrctype from cmp_apply where opsrctype=1  and cmp_apply.pk_apply=yer_task.billid ) \n");
		}
		if ("er_mtapp_bill".equalsIgnoreCase(tableName.trim())) {// zhangjxh+
			whereSql.append(" and exists(select dr from " + tableName
					+ " where ISNULL (dr , 0 ) = 0 ) \n");

		} 
		whereSql.append("and (billtype.islock ='N' or billtype.islock is null)\n");// houmeng3+
		// // houmeng3+模糊搜索修改为单据编号，日期，金额
		// if (StringUtils.isNotEmpty(param.getLikeQueryValue())) {
		// whereSql.append(" and  (convert(char(10),yer_task.billdate,120) like'%"
		// + param.getLikeQueryValue() + "%'\n");
		// whereSql.append(" or yer_task.billno like'%"
		// + param.getLikeQueryValue() + "%'\n");
		// whereSql.append(" or yer_task.total like'%"
		// + param.getLikeQueryValue() + "%')\n");
		//
		// }

		whereSql.append(" and isnull(yer_task.dr,0)=0 \n");// houmeng3+
		if (!"".equals(param.getBillTypeCode())
				&& param.getBillTypeCode() != null
				&& !"000".equals(param.getBillTypeCode())
				&& !"4641".equals(param.getBillTypeCode())
				&& !"4642".equals(param.getBillTypeCode())
				&& !"35".equals(param.getBillTypeCode())) {
			whereSql.append("and yer_task.pk_billtype='" + billTypeCode + "'\n");
		}

		if (!"".equals(param.getLinkId()) && param.getLinkId() != null) {
			UFDate[] date = YerExpenseApproveUtil.getBillDates(param.getLinkId());
			whereSql.append(" and  yer_task.ts >='"
					+ date[0].toLocalString().substring(0, 10) + "'\n");
		}
		if (param.isApprove()) {
			whereSql.append(" and yer_task.approvestatus='1' and yer_task.approveresult is not null\n");
		} else {
			whereSql.append(" and yer_task.approvestatus in(0,2)\n");
		}
		if (shortValuesMap != null && shortValuesMap.size() > 0) {
			whereSql.append(" and yer_task.billid in("
					+ shortValuesMap.get(map.get(key).getCode()) + ")\n");
		}
		whereSql.append(" ) yer_task \n");
		if (!"".equals(param.getWheresql()) && param.getWheresql() != null) {
			whereSql.append("where " + param.getWheresql() + "\n");
		}
		return whereSql.toString();
	}

	public String getParamWhereSql(YerTaskQueryParamVO param,
			Map<String, PortalQueryFieldName> map, String billTypeCode,
			Map<String, String> shortValuesMap, Boolean isDetail) {
		// Map<String, String> billTypeTableMap =
		// ExpenseApproveUtil.getBillTypeToMasterTable();
		// Map<String, String> billQueryFieldMap =
		// ExpenseApproveUtil.getBillTypeToQueryField();
		// Map<String, String> billIDFieldMap =
		// ExpenseApproveUtil.getBillIDField();
		// Map<String, String> billMakerFieldMap =
		// ExpenseApproveUtil.getBillMakerField();
		// Map<String, String> billDeptFieldMap =
		// ExpenseApproveUtil.getBillDeptField();
		StringBuffer whereTypeSql = new StringBuffer();
		Iterator<Entry<String, PortalQueryFieldName>> ite = map.entrySet()
				.iterator();
		while (ite.hasNext()) {
			Entry<String, PortalQueryFieldName> next = ite.next();
			String key = next.getKey();
			String tableName = next.getValue().getmTableName();
			if (whereTypeSql != null && whereTypeSql.length() > 0) {
				whereTypeSql.append(" or ");
			}
			if ("er_mtapp_bill".equalsIgnoreCase(tableName.trim())) {// zhangjxh+
				whereTypeSql.append(" (billtype.pk_billtypecode like '" + key
						+ "%' and exists(select dr from " + tableName
						+ " where ISNULL (dr , 0 ) = 0 )) ");

			} else if ("36D1".equals(key)) {
				whereTypeSql
						.append(" (billtype.pk_billtypecode like '"
								+ key
								+ "%' and exists (select opsrctype from cmp_apply where cmp_apply.pk_apply=yer_task.billid and opsrctype=1)) ");

			} else if ("F0".equals(key)) {
				whereTypeSql.append(" (billtype.pk_billtypecode = 'D0' or billtype.pk_billtypecode like '" + key + "%') ");
			} else if ("F1".equals(key)) {
				whereTypeSql.append(" (billtype.pk_billtypecode = 'D1' or billtype.pk_billtypecode like '" + key + "%') ");
			} else if ("F2".equals(key)) {
				whereTypeSql.append(" (billtype.pk_billtypecode = 'D2' or billtype.pk_billtypecode like '" + key + "%') ");
			} else if ("F3".equals(key)) {
				whereTypeSql.append(" (billtype.pk_billtypecode = 'D3' or billtype.pk_billtypecode like '" + key + "%') ");
			} else if("FIV".equals(key)){
				whereTypeSql.append(" (billtype.pk_billtypecode = 'FP0' or billtype.pk_billtypecode like '" + key + "%') ");
			}else if("264".equals(key)){
				whereTypeSql.append(" billtype.pk_billtypecode in (select djlxbm from er_djlx where djlxbm like '264%' and bxtype !='2')");//去掉调整单
			}
//			else if("262".equals(key)){
//				//去掉费用预提单 qiufh添加
//				whereTypeSql.append(" 1<>1 ");
//			}
			else{
				whereTypeSql.append(" billtype.pk_billtypecode like '" + key + "%' ");
			}

		}

		String selectField = "select yer_task.billno,yer_task.dr,yer_task.billid as billid,billtype.pk_billtypeid as defitem3,billtype.PK_BILLTYPECODE as billtype,"
				+ "yer_task.pk_wf_task as defitem2,yer_task.ts as receivetime,yer_task.pk_checkflow as defitem7 ";
		StringBuffer whereSql = new StringBuffer();
		whereSql.append("select * from (");
		whereSql.append(selectField + " from pub_workflownote yer_task\n")
				.append("join bd_billtype billtype on yer_task.PK_BILLTYPE=billtype.PK_BILLTYPECODE\n")
				.append("where  yer_task.actiontype like 'Z%'\n")
				.append("and yer_task.checkman='" + getReceiver() + "'\n")
				.append("and (billtype.PK_GROUP='" + YerPortalUtil.getPK_group()
						+ "' or billtype.PK_GROUP ='~') \n");

		if (whereTypeSql.length() > 0) {
			whereSql.append(" and ( " + whereTypeSql.toString() + " ) ");
		}
		whereSql.append("and (billtype.islock ='N' or billtype.islock is null)\n");// houmeng3+

		whereSql.append(" and isnull(yer_task.dr,0)=0 \n");// houmeng3+
		if (!"".equals(param.getBillTypeCode())
				&& param.getBillTypeCode() != null
				&& !"000".equals(param.getBillTypeCode())
				&& !"4641".equals(param.getBillTypeCode())
				&& !"4642".equals(param.getBillTypeCode())
				&& !"35".equals(param.getBillTypeCode())) {
			whereSql.append("and yer_task.pk_billtype='" + billTypeCode + "'\n");
		}

		if (!"".equals(param.getLinkId()) && param.getLinkId() != null) {
			UFDate[] date = YerExpenseApproveUtil.getBillDates(param.getLinkId());
			whereSql.append(" and  yer_task.ts >='"
					+ date[0].toLocalString().substring(0, 10) + "'\n");
		}
		if (param.isApprove()) {
			whereSql.append(" and yer_task.approvestatus='1' and yer_task.approveresult is not null\n");
		} else {
			whereSql.append(" and yer_task.approvestatus in(0,2)\n");
		}
		// if (shortValuesMap != null && shortValuesMap.size() > 0) {
		// whereSql.append(" and yer_task.billid in("
		// + shortValuesMap.get(map.get(key).getCode()) + ")\n");
		// }
		whereSql.append(" ) yer_task \n");
		if (!"".equals(param.getWheresql()) && param.getWheresql() != null) {
			whereSql.append("where " + param.getWheresql() + "\n");
		}
		return whereSql.toString();
	}

	@Override
	public int getAllApproveCount(YerTaskQueryParamVO paramVO)
			throws BusinessException {

		StringBuffer querySql = new StringBuffer();
		querySql.append("select count(1) from (")
				.append(getBillApproveSql(paramVO, false, null)).append(") a");
		BaseProcessor baseProcessor = null;

		baseProcessor = new ColumnProcessor(1);
		Object value = executeQuery(querySql.toString(), baseProcessor);
		if (value != null) {
			return Integer.parseInt(value.toString());
		}
		return 0;
	}

	/*
	 * 适配供应链客户费用申请单 author zhangjxh+ begin
	 */
	public Map<String, List<BillApproveNav>> filterMEData(
			List<BillApproveNav> result) {

		List<BillApproveNav> removeList = new ArrayList<BillApproveNav>();
		List<BillApproveNav> approveResult = new ArrayList<BillApproveNav>();
		Map<String, List<BillApproveNav>> resultMap = new HashMap<String, List<BillApproveNav>>();
		BillApproveNav clientFeeApplyban = new BillApproveNav();
		int ClinetFeeApplyCount = 0;
		clientFeeApplyban.setBillTypeCode("4641");
		clientFeeApplyban.setBillTypeName("客户费用申请单");
		BillApproveNav clientFeeban = new BillApproveNav();
		int ClinetFeeCount = 0;
		clientFeeban.setBillTypeCode("35");
		clientFeeban.setBillTypeName("客户费用单");
		BillApproveNav ZCXPBan = new BillApproveNav();
		int ZCXPcount = 0;
		ZCXPBan.setBillTypeCode("4642");
		ZCXPBan.setBillTypeName("助促销品申请单");
		for (BillApproveNav bill : result) {
			if (bill.getBillTypeCode().contains("261X")) {// 处理供应量单据升级后的单据类型变为261X—
				String parentbilltype = PfDataCache.getBillType(
						bill.getBillTypeCode()).getParentbilltype();
				String systemCode = PfDataCache.getBillType(
						bill.getBillTypeCode()).getSystemcode();
				if ("me".equalsIgnoreCase(systemCode)
						&& "4641".equals(parentbilltype)) {
					ClinetFeeApplyCount = ClinetFeeApplyCount
							+ Integer.valueOf(bill.getApproveCount());
					clientFeeApplyban.setApproveCount(ClinetFeeApplyCount + "");
					removeList.add(bill);
				} else if ("me".equalsIgnoreCase(systemCode)
						&& "4642".equals(parentbilltype)) {
					ZCXPcount = ZCXPcount
							+ Integer.valueOf(bill.getApproveCount());
					ZCXPBan.setApproveCount(ZCXPcount + "");
					removeList.add(bill);
				}
			} else if (bill.getBillTypeCode().contains("4641")) {

				ClinetFeeApplyCount = ClinetFeeApplyCount
						+ Integer.valueOf(bill.getApproveCount());
				clientFeeApplyban.setApproveCount(ClinetFeeApplyCount + "");

				removeList.add(bill);
			} else if (bill.getBillTypeCode().startsWith("35")) {
				ClinetFeeCount = ClinetFeeCount
						+ Integer.valueOf(bill.getApproveCount());
				clientFeeban.setApproveCount(ClinetFeeCount + "");

				removeList.add(bill);
			} else if (bill.getBillTypeCode().contains("4642")) {

				ZCXPcount = ZCXPcount + Integer.valueOf(bill.getApproveCount());
				ZCXPBan.setApproveCount(ZCXPcount + "");

				removeList.add(bill);
			}

		}
		if (clientFeeApplyban.getApproveCount() != null
				&& !clientFeeApplyban.getApproveCount().trim().equals("0")) {
			approveResult.add(clientFeeApplyban);
		}
		if (clientFeeban.getApproveCount() != null
				&& !clientFeeban.getApproveCount().trim().equals("0")) {
			approveResult.add(clientFeeban);
		}
		if (ZCXPBan.getApproveCount() != null
				&& !ZCXPBan.getApproveCount().trim().equals("0")) {
			approveResult.add(ZCXPBan);
		}
		resultMap.put(REMOVELIST, removeList);
		resultMap.put(APPROVERESULT, approveResult);
		return resultMap;

	}
	private Object executeQuery(String sql, ResultSetProcessor processor) throws BusinessException{
		if(!SscIntergrationUtil.isSscDeploy()){
			return new BaseDAO().executeQuery(sql,new SQLParameter(),processor);
		}
		return NCLocator.getInstance().lookup(IYerQueryService.class).executeQuery(sql, processor);
	}
	public  List<SuperVO> getVOBySql(PaginationInfo pinfo,
			String querySql, Class<?> classType) throws BusinessException {
		if(!SscIntergrationUtil.isSscDeploy()){
			return YerExpenseApproveUtil.getVOBySql(pinfo,querySql.toString(), YerTaskVO.class);
		}
		int index = pinfo.getPageIndex();
		int pageSize = pinfo.getPageSize();
		int skip = index * pageSize;
		BaseProcessor baseProcessor = null;
		List<SuperVO> voList = new ArrayList<SuperVO>();
		try {
			if (pageSize > 0) {
				baseProcessor = new LfwBeanPagedListProcessor(classType, skip,pageSize);
				PagedResult result = (PagedResult) executeQuery(querySql, baseProcessor);
				List<SuperVO> superVos = result.getResult();
				int billnumSum=result==null?0:result.getSize();
				AppUtil.addAppAttr("billnumSum", billnumSum);//houmeng3+ 获取查出单据数量，减少一次sql查询
				pinfo.setRecordsCount(result.getSize());
				int size = superVos.size();
				if (size > 0) {
					for (int i = 0; i < size; i++) {
						voList.add(superVos.get(i));
					}
				}
			} else {
				baseProcessor = new BeanListProcessor(YerTaskVO.class);
				voList = (List<SuperVO>) executeQuery(querySql.toString(), baseProcessor);
			}
		} catch (DAOException e) {
			Logger.error(e.getMessage(), e);
		}

		return voList;
	}

}
